package com.spittr.db.jdbc;

import com.spittr.db.ManagerRepository;
import com.spittr.domain.Manager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * JdbcManagerRepository
 *
 * @author 龙虹宇
 * @version 1.0
 * @date 2023 /11/17
 */
@Repository
public class JdbcManagerRepository implements ManagerRepository {

    private JdbcTemplate jdbc;

    private static final String INSERT_MANAGER = "insert into Manager (username, password, full_name, email, phone_no, delete) values (?, ?, ?, ?, ?, ?)";

    private static final String SELECT_MANAGER = "select id, username, password, full_name, email, phone_no, `delete` from Manager";

    private static final String UPDATE_MANAGER_DEL = "update Manager set delete=?";

    private static final String UPDATE_MANAGER = "update Manager set username=?, password=?, full_name=?, email=?, phone_no=?";

    /**
     * Instantiates a new Jdbc manager repository.
     *
     * @param jdbc the jdbc
     */
    @Autowired
    public JdbcManagerRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Override
    public long count() {
        return jdbc.queryForLong("select count(id) from Manager");
    }

    @Override
    public Manager save(Manager manager) {
        jdbc.update(INSERT_MANAGER, manager.getUsername(), manager.getPassword(), manager.getFullName(),
                manager.getEmail(), manager.getPhoneNo(), manager.getDelete());
        return manager;
    }

    @Override
    public Manager findOne(long id) {
        Manager manager = null;
        try {
            manager = jdbc.queryForObject(SELECT_MANAGER + " where id=?", new FullManagerRowMapper(), id);
        } catch (DataAccessException e){
        }
        return manager;
    }

    @Override
    public Manager findByUserName(String username) {
        Manager manager = null;
        try {
            manager = jdbc.queryForObject(SELECT_MANAGER + " where username=?", new ManagerRowMapper(), username);
        } catch (DataAccessException e) {
        }
        return manager;
    }

    @Override
    public Manager findByUserNameExceptCertainId(String username, int id) {
        Manager manager = null;
        try {
            manager = jdbc.queryForObject(SELECT_MANAGER + " where username=? and id!=?", new ManagerRowMapper(), username, id);
        } catch (DataAccessException e) {
        }
        return manager;
    }

    @Override
    public Manager findByUserNameAndPassword(String username, String password) {
        Manager manager = null;
        try {
            manager = jdbc.queryForObject(SELECT_MANAGER + " where username=? and password=? and `delete`=0", new FullManagerRowMapper(), username, password);
        } catch (DataAccessException e) {
        }
        return manager;
    }

    @Override
    public List<Manager> findAll() {
        return jdbc.query(SELECT_MANAGER + " order by id", new ManagerRowMapper());
    }

    @Override
    public List<Manager> selectByPage(int currentPage, int size) {
        return jdbc.query(SELECT_MANAGER + " limit " + currentPage + ", " + size, new FullManagerRowMapper() );
    }

    @Override
    public int deleteById(int id) {
        return jdbc.update(UPDATE_MANAGER_DEL + " where id=" + id, 1);
    }

    @Override
    public int updateById(int id, Manager manager) {
        return jdbc.update(UPDATE_MANAGER + " where id=" + id, manager.getUsername(), manager.getPassword(),
                manager.getFullName(), manager.getEmail(), manager.getPhoneNo());
    }

    private static class ManagerRowMapper implements RowMapper<Manager> {

        @Override
        public Manager mapRow(ResultSet rs, int i) throws SQLException {
            return new Manager(rs.getInt("id"), rs.getString("username"), null, rs.getString("full_name"),
                    rs.getString("email"), rs.getString("phone_no"), rs.getInt("delete"));
        }
    }

    
    private static class FullManagerRowMapper implements RowMapper<Manager> {

        @Override
        public Manager mapRow(ResultSet rs, int i) throws SQLException {
            return new Manager(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("full_name"),
                    rs.getString("email"), rs.getString("phone_no"), rs.getInt("delete"));
        }
    }
}
